package com.mtsmda.daoImpl;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.jws.WebService;

import org.apache.commons.lang.StringUtils;

import com.mtsmda.daoI.CustomerDAOI;
import com.mtsmda.is.admin.domain.Customer;
import com.mtsmda.is.admin.domain.util.db.CheckConnection;
import com.mtsmda.is.admin.domain.util.db.CreateConnection;

@WebService(endpointInterface = "com.mtsmda.daoI.CustomerDAOI")
public class CustomerDAOImpl implements CustomerDAOI {

	private CreateConnection createConnectionToDB = CreateConnection
			.getInstance(new File("db.properties"));
	private CallableStatement callableStatement;

	public WrapperCustomer getAllCustomers() {
		ArrayList<Customer> customers = null;

		try {
			if (CheckConnection.checkConn(createConnectionToDB)) {
				Connection connection = createConnectionToDB.getConnection();
				callableStatement = connection
						.prepareCall("call getCustomers()");
				ResultSet resultSet = callableStatement.executeQuery();
				customers = fillList(resultSet);
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		WrapperCustomer customer = new WrapperCustomer();
		customer.setCustomers(customers);
		return customer;
	}

	@Override
	public Customer getCustomer(Integer customerId) {
		Customer customer = null;
		ResultSet resultSet = null;
		try {
			if (CheckConnection.checkConn(createConnectionToDB)) {
				Connection connection = createConnectionToDB.getConnection();
				callableStatement = connection
						.prepareCall("call getCustomer(?)");
				callableStatement.setInt(1, customerId);
				resultSet = callableStatement.executeQuery();
				System.out.println(resultSet != null);
				List<Customer> fillList = fillList(resultSet);
				if (fillList != null && fillList.get(0) != null) {
					customer = fillList.get(0);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			createConnectionToDB.finalize();
		}
		return customer;
	}

	@Override
	public boolean insertCustomer(Customer customer) {
		try {
			if (CheckConnection.checkConn(createConnectionToDB)) {
				Connection connection = createConnectionToDB.getConnection();
				callableStatement = connection
						.prepareCall("call insertCustomer(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				callableStatement.setString(1, customer
						.getOrganisationOrPerson().toString());
				callableStatement.setString(2, customer.getOrganisationName());
				callableStatement.setString(3, customer.getGender().toString());
				callableStatement.setString(4, customer.getFirstName());
				callableStatement.setString(5, customer.getMiddleName());
				callableStatement.setString(6, customer.getLastName());
				callableStatement.setString(7, customer.getPostIndex());
				callableStatement.setString(8, customer.getPhoneNumber());
				callableStatement.setString(9, customer.getMobilePhoneNumber());
				callableStatement.setDate(10, new Date(customer
						.getDateOfBirth().getTime()));
				callableStatement.setString(11, customer.getPostIndex());
				int result = callableStatement.executeUpdate();
				if (result > 0) {
					return true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			createConnectionToDB.finalize();
		}
		return false;
	}

	@Override
	public boolean updateCustomer(Customer customer) {
		try {
			if (CheckConnection.checkConn(createConnectionToDB)) {
				Connection connection = createConnectionToDB.getConnection();
				callableStatement = connection
						.prepareCall("call updateCustomer(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				callableStatement.setInt(1, customer.getCustomerId());
				callableStatement.setString(2, customer
						.getOrganisationOrPerson().toString());
				callableStatement.setString(3, customer.getOrganisationName());
				callableStatement.setString(4, customer.getGender().toString());
				callableStatement.setString(5, customer.getFirstName());
				callableStatement.setString(6, customer.getMiddleName());
				callableStatement.setString(7, customer.getLastName());
				callableStatement.setString(8, customer.getPostIndex());
				callableStatement.setString(9, customer.getPhoneNumber());
				callableStatement
						.setString(10, customer.getMobilePhoneNumber());
				callableStatement.setDate(11, new Date(customer
						.getDateOfBirth().getTime()));
				callableStatement.setString(12, customer.getPostIndex());
				int result = callableStatement.executeUpdate();
				if (result > 0) {
					return true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			createConnectionToDB.finalize();
		}
		return false;
	}

	@Override
	public boolean deleteCustomer(Customer customer) {
		try {
			if (CheckConnection.checkConn(createConnectionToDB)) {
				Connection connection = createConnectionToDB.getConnection();
				callableStatement = connection
						.prepareCall("call deleteCustomer(?)");
				callableStatement.setInt(1, customer.getCustomerId());
				int result = callableStatement.executeUpdate();
				if (result > 0) {
					return true;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			createConnectionToDB.finalize();
		}
		return false;
	}

	public ArrayList<Customer> fillList(ResultSet resultSet) throws Exception {
		ArrayList<Customer> customers = null;
		if (resultSet != null) {
			customers = new ArrayList<>();
			while (resultSet.next()) {
				Customer customer = new Customer();

				customer.setCustomerId(resultSet.getInt("customerId"));

				customer.setOrganisationOrPerson(resultSet
						.getString("organisationOrPerson") != null ? resultSet
						.getString("organisationOrPerson").charAt(0) : ' ');

				if (StringUtils.isNotBlank(resultSet
						.getString("organisationName")))
					customer.setOrganisationName(resultSet
							.getString("organisationName"));

				if (StringUtils.isNotBlank(resultSet.getString("firstName"))) {
					customer.setFirstName(resultSet.getString("firstName"));
				}

				if (StringUtils.isNotBlank(resultSet.getString("middleName"))) {
					customer.setMiddleName(resultSet.getString("middleName"));
				}

				if (StringUtils.isNotBlank(resultSet.getString("lastName"))) {
					customer.setLastName(resultSet.getString("lastName"));
				}

				if (StringUtils.isNotBlank(resultSet.getString("emailPost"))) {
					customer.setEmailPost(resultSet.getString("emailPost"));
				}

				if (StringUtils.isNotBlank(resultSet.getString("phoneNumber"))) {
					customer.setPhoneNumber(resultSet.getString("phoneNumber"));
				}

				if (StringUtils.isNotBlank(resultSet
						.getString("mobilePhoneNumber"))) {
					customer.setMobilePhoneNumber(resultSet
							.getString("mobilePhoneNumber"));
				}

				if (resultSet.getDate("dateOfBirth") != null) {
					customer.setDateOfBirth(resultSet.getDate("dateOfBirth"));
				}

				if (StringUtils.isNotBlank(resultSet.getString("postIndex"))) {
					customer.setPostIndex(resultSet.getString("postIndex"));
				}
				customers.add(customer);
			}

		}
		return customers;
	}

}